package com.example.accessconnector.util;

/**
 * @author 15037
 * @description
 * @date 2025-04-25 18:10
 */

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class AccessDatabaseCreator {

    /**
     * db路径
     */
    private String dbPath;

    public String getDbPath() {
        return dbPath;
    }

    public void setDbPath(String dbPath) {
        this.dbPath = dbPath;
    }

    public AccessDatabaseCreator(String dbPath) throws ClassNotFoundException {
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); // 显示加载驱动
        this.dbPath = dbPath;
    }

    /**
     * @param dbPath 指定数据库路径
     */
    public void createNewAccessDatabaseNotExists() {
        try {
            // 检查数据库文件是否存在
            File dbFile = new File(this.dbPath);

            if (!dbFile.exists()) {
                // 确保父目录存在
                dbFile.getParentFile().mkdirs();

                // 创建新数据库
                createNewAccessDatabase();
                System.out.println("成功创建新的Access数据库: " + this.dbPath);

            } else {
                System.out.println("数据库已存在: " + this.dbPath);
            }

            // 测试连接
            testConnection();

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("创建access数据库失败");
        }
    }

    private void createNewAccessDatabase() throws Exception {
        // UCanAccess 会在连接时自动创建不存在的数据库
        String connectionUrl = "jdbc:ucanaccess://" + dbPath + ";newdatabaseversion=V2010";

        // 建立连接会自动创建数据库
        try (Connection conn = DriverManager.getConnection(connectionUrl);
             Statement stmt = conn.createStatement()) {
            // 连接成功后立即关闭，数据库文件已创建
        }
    }


    public void executeSql(String sql) throws Exception {
        String connectionUrl = "jdbc:ucanaccess://" + dbPath;

        try (Connection conn = DriverManager.getConnection(connectionUrl);
             Statement stmt = conn.createStatement()) {

            // 创建Employees表
            String createEmployeeTable = sql;

            stmt.execute(createEmployeeTable);

        }
    }


    public List<HashMap<String, Object>> executeQuery(String query) {
        List<HashMap<String, Object>> results = new ArrayList<>();

        try {
            // 构建连接字符串
            String connStr = "jdbc:ucanaccess://" + dbPath;

            // 建立连接
            try (Connection conn = DriverManager.getConnection(connStr);
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(query)) {

                // 获取结果集元数据
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();


                // 处理结果集
                while (rs.next()) {
                    HashMap<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        row.put(columnName, rs.getObject(i));
                    }
                    results.add(row);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询失败", e);
        }

        return results;
    }


    public boolean testConnection() throws Exception {
        String connectionUrl = "jdbc:ucanaccess://" + dbPath;

        try (Connection conn = DriverManager.getConnection(connectionUrl)) {
            System.out.println("成功连接到数据库: " + dbPath);

            // 获取数据库元数据
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("数据库产品名称: " + meta.getDatabaseProductName());
            System.out.println("数据库版本: " + meta.getDatabaseProductVersion());
            return true;
        } catch (Exception e) {
            return false;
        }
    }


    public boolean isTableExist(String tableName) throws Exception {
        List<HashMap<String, Object>> hashMaps = this.executeQuery("select * from INFORMATION_SCHEMA.Tables WHERE TABLE_NAME = '" + tableName.toUpperCase() + "'");
        return hashMaps.size() > 0;
    }
}
